package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import domain.Doctor;
import domain.Owner;
import domain.Pet;
import util.C3P0Util;

public class DoctorDaoImpl {
	
	/**
	 * 查看所有宠物医生信息
	 * @return
	 * @throws SQLException
	 */
	public List<Doctor> findAllDoctors() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from doctor", new BeanListHandler<Doctor>(Doctor.class));
	}
	/**
	 * 添加医生信息
	 * @param doctor
	 * @throws SQLException
	 */
	public void addDoctor(Doctor doctor) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("INSERT INTO doctor VALUES(?,?,?,?,?)",doctor.getDoctorId(),doctor.getDoctorName(),doctor.getExpertise(),doctor.getDoctorPhone(),doctor.getCredentials());
	}
	
	/**
	 * 通过doctorId找到医生信息
	 * @param doctorId
	 * @return 
	 * @throws SQLException 
	 */
	public Doctor findDoctorById(String doctorId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from doctor where doctorId=?", new BeanHandler<Doctor>(Doctor.class),doctorId);
	}
	
	/**
	 * 更新医生信息
	 * @param doctor
	 * @throws SQLException
	 */
	public void updateDoctor(Doctor doctor) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("update doctor set doctorName=?,doctorPhone=?,expertise=?,credentials=? where doctorId=?",
				doctor.getDoctorName(),doctor.getDoctorPhone(),doctor.getExpertise(),doctor.getCredentials(),doctor.getDoctorId());
		
		
	}
	/**
	 * 查找指定医生治疗的的所有宠物
	 * @param doctorId
	 * @return
	 * @throws SQLException
	 */
	public List<Pet> findPetsByDoctorId(String doctorId) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from pet where doctorId=?", new BeanListHandler<Pet>(Pet.class),doctorId);
	}
	/**
	 * 通过doctorId删除医生信息
	 * @param doctorId
	 * @throws SQLException
	 */
	public void deleteDoctorById(String doctorId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("delete from doctor where doctorId=?",doctorId );
	}
	/**
	 * 多条件查询，记住哪些信息，就查哪些信息
	 * @param doctorId
	 * @param doctorName
	 * @param expertise
	 * @param credentials
	 * @param doctorPhone
	 * @return
	 * @throws SQLException
	 */
	public List<Doctor> findDoctorByConditions(String doctorId,
			String doctorName, String expertise, String credentials,
			String doctorPhone) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from doctor where 1=1";
		List list = new ArrayList();
		if(!"".equals(doctorId.trim())){
			sql+=" and doctorId like ?"; //  不能在这写%   %'1002'%
			list.add("%"+doctorId.trim()+"%");// '%1002%'
		}
		
		if(!"".equals(doctorName.trim())){
			sql+=" and doctorName like ?";
			list.add("%"+doctorName.trim()+"%");
		}
		
		if(!"".equals(expertise.trim())){
			sql+=" and expertise like ?";
			list.add("%"+expertise.trim()+"%");
		}
		
		if(!"".equals(credentials.trim())){
			sql+=" and credentials like ?";
			list.add("%"+credentials.trim()+"%");
		}
		if(!"".equals(doctorPhone.trim())){
			sql+=" and doctorPhone like ?";
			list.add("%"+doctorPhone.trim()+"%");
		}
		
		return qr.query(sql, new BeanListHandler<Doctor>(Doctor.class),list.toArray());
	}
	
}
